package idv.arthur.work;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

/**
 * Servlet implementation class SaveRoleUserMapping
 */
//@WebServlet(description = "將角色與所選擇的ID紀錄下來", urlPatterns = { "/SaveRoleUserMapping" })
public class SaveRoleUserMapping extends HttpServlet {
	private static final long serialVersionUID = 1L;
       
    /**
     * @see HttpServlet#HttpServlet()
     */
    public SaveRoleUserMapping() {
        super();
    }

	/**
	 * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		request.setCharacterEncoding("UTF-8");
		
		Widgets wid = new Widgets();
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		int roleID = Integer.parseInt(request.getParameter("userType"));
		String uList =  request.getParameter("userList");
		String[] userList = uList.split(","); 
		String strSQL = "DELETE FROM tms_roleusermapping WHERE roleID=?";
		String returnResult ="設定完成!";
		String loginUser = request.getParameter("loginUser");
		
		try {
			conn = wid.getConn(true);
			ps = conn.prepareStatement(strSQL);
			ps.setInt(1, roleID);
			ps.execute();
			
			/** 查詢這個 role能有那些畫面 **/
			strSQL = "SELECT t2.typeSN, t1.detailSN FROM activiti.tms_role_auth_mapping t1, tms_authorization_desc t2 WHERE t1.roleID=? AND t1.detailSN = t2.detailSN AND t1.detailSN<>15";
			ps = conn.prepareStatement(strSQL);
			ps.setString(1, String.valueOf(roleID));
			rs = ps.executeQuery();
			
			while (rs.next()){
				/** 將所有擁有role的這個畫面的使用者，其權限通通刪除 **/
				strSQL = "DELETE FROM tms_authorization_body WHERE typeSN = ? AND detailSN = ?";
				ps = conn.prepareStatement(strSQL);
				ps.setInt(1, rs.getInt("typeSN"));
				ps.setInt(2, rs.getInt("detailSN"));
				ps.execute();	
			}
			
			for(int i=0;i<userList.length;i++) {
				if (!userList[i].trim().equalsIgnoreCase("")){
					strSQL ="INSERT INTO tms_roleusermapping (roleID,userID) VALUES (?,?)";
					ps = conn.prepareStatement(strSQL);
					ps.setInt(1, roleID);
					ps.setString(2, userList[i]);
					ps.execute();
					
					/** 再查一次這個 role能有那些畫面，要進行新增 **/
					strSQL = "SELECT t2.typeSN, t1.detailSN FROM activiti.tms_role_auth_mapping t1, tms_authorization_desc t2 WHERE t1.roleID=? AND t1.detailSN = t2.detailSN AND t1.detailSN<>15";
					ps = conn.prepareStatement(strSQL);
					ps.setString(1, String.valueOf(roleID));
					rs = ps.executeQuery();
					
					while (rs.next()){
						/** 將這個role的這個畫面，指派給這位使用者 **/
						strSQL = "INSERT INTO activiti.tms_authorization_body (typeSN, detailSN, userId,creator, CreatedTime) VALUES (?,?,?,?,?)";
						ps = conn.prepareStatement(strSQL);
						ps.setInt(1, rs.getInt("typeSN"));
						ps.setInt(2, rs.getInt("detailSN"));
						ps.setString(3, userList[i]);
						ps.setString(4, loginUser);
						ps.setTimestamp(5, wid.currentTimeStamp());
						ps.execute();	
					}
				}
			}
			
		} catch (SQLException e) {
			returnResult = wid.keepErrorMes(conn, ps, e, "", true);
		} finally {
			try {
				if (rs!= null) {rs.close();}
				if (ps!= null) {ps.close();}
				if (conn!= null) {conn.close();}
			} catch (SQLException e) {
				returnResult = e.toString();
				System.out.println(returnResult);
			}
		}
		
		response.setContentType("application/text");
		response.setCharacterEncoding("UTF-8");
		response.getWriter().write(returnResult);	
	}
}